/*
 * @Description: ------------ fileDescription -----------
 * @Author: snows_l snows_l@163.com
 * @Date: 2024-04-01 09:35:52
 * @LastEditors: snows_l snows_l@163.com
 * @LastEditTime: 2024-12-19 09:50:46
 * @FilePath: \Backstage\Server\src\router\favors.js
 */
const express = require('express');
const db = require('../utils/connDB');
const Excel = require('exceljs');
const { verifyToken } = require('../utils/handleToken');
const multer = require('multer');
const { handleError } = require('../utils/common.js');
const moment = require('moment');
const fs = require('fs');

const router = express.Router();

// 分页查询人情来往列表
router.get('/list', (req, res) => {
  let token = req.cookies['bs-cookie'];
  const { dictType, name, page, size } = req.query;
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');
    let offset = (page - 1) * size;
    let sql = `select * from favors limit ${size} offset ${offset};`;
    let lenSql = `select count('favors_id') from favors`;
    if (user.role_key == 'admin') {
      if (dictType) {
        sql = `select * from favors where favors_timer = '${dictType}' limit ${size} offset ${offset};`;
        lenSql = `select count('favors_id') from favors where favors_timer = '${dictType}';`;
        if (name) {
          sql = `select * from favors where favors_timer = '${dictType}' and favors_name like '%${name}%' limit ${size} offset ${offset};`;
          lenSql = `select count('favors_id') from favors where favors_timer = '${dictType}' and favors_name like '%${name}%';`;
        }
      } else {
        if (name) {
          sql = `select * from favors where favors_name like '%${name}%' limit ${size} offset ${offset}`;
          lenSql = `select count('favors_id') from favors where favors_name like '%${name}%';`;
        }
      }
    } else {
      sql = `select * from favors where role_key = '${user.role_key}' limit ${size} offset ${offset};`;
      lenSql = `select count('favors_id') from favors where role_key = '${user.role_key}'`;
      if (dictType) {
        sql = `select * from favors where favors_timer = '${dictType}' and role_key = '${user.role_key}' limit ${size} offset ${offset};`;
        lenSql = `select count('favors_id') from favors where favors_timer = '${dictType}' and role_key = '${user.role_key}';`;
        if (name) {
          sql = `select * from favors where favors_timer = '${dictType}' and favors_name like '%${name}%' and role_key = '${user.role_key}' limit ${size} offset ${offset};`;
          lenSql = `select count('favors_id') from favors where favors_timer = '${dictType}' and favors_name like '%${name}%' and role_key = '${user.role_key}';`;
        }
      } else {
        if (name) {
          sql = `select * from favors where favors_name like '%${name}%' and role_key = '${user.role_key}' limit ${size} offset ${offset}`;
          lenSql = `select count('favors_id') from favors where favors_name like '%${name}%' and role_key = '${user.role_key}';`;
        }
      }
    }
    db.queryAsync(lenSql)
      .then(count => {
        db.queryAsync(sql)
          .then(res1 => {
            let data = res1.results;
            let total = count.results[0]["count('favors_id')"];
            res.send({
              code: 200,
              data: data,
              msg: 'success',
              total: total
            });
          })
          .catch(error => {
            handleError(res, error);
          });
      })
      .catch(error => {
        handleError(res, error);
      });
  } catch (error) {
    handleError(res, error);
  }
});

// 编辑人情来往
router.post('/edit', (req, res) => {
  let token = req.cookies['bs-cookie'];
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');
    let { favors_id, favors_name, favors_timer, favors_money, favors_backMoney, remark, role_key, role_str } = req.body;
    let mobileSql = `update favors set favors_backMoney = '${favors_backMoney}', update_time = '${new Date()}' where favors_id = '${favors_id}';`;
    let sql = `update favors set favors_name = '${favors_name}', favors_timer = '${favors_timer}', favors_money = '${favors_money}', favors_backMoney = '${favors_backMoney}', remark = '${remark}', role_key = '${
      role_key ? role_key : user.role_key
    }', role_str = '${role_str ? role_str : user.role_str}', update_time = '${new Date()}' where favors_id = '${favors_id}';`;
    db.queryAsync(favors_name ? sql : mobileSql)
      .then(res1 => {
        res.send({
          code: 200,
          data: res1.results,
          msg: 'success'
        });
      })
      .catch(err => {
        handleError(res, err);
      });
  } catch (error) {
    handleError(res, error);
  }
});

// 新增人情来往
router.post('/add', (req, res) => {
  let token = req.cookies['bs-cookie'];
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');
    let { favors_name, favors_timer, favors_money, favors_backMoney, remark, role_key, role_str } = req.body;
    let sql = `insert into favors (favors_name, role_key, role_str, favors_timer, favors_money, favors_backMoney, remark, create_time) values ('${favors_name}', '${
      role_key ? role_key : user.role_key
    }', '${role_str ? role_str : user.role_str}', '${favors_timer}', '${favors_money}', '${favors_backMoney}', '${remark}', '${new Date()}');`;
    db.queryAsync(sql)
      .then(res1 => {
        res.send({
          code: 200,
          data: res1.results,
          msg: 'success'
        });
      })
      .catch(err => {
        handleError(res, err);
      });
  } catch (error) {
    handleError(res, error);
  }
});

// 删除
router.delete('/del', (req, res) => {
  let token = req.cookies['bs-cookie'];
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');
    let { id } = req.query;
    let sql = `delete from favors where favors_id = '${id}';`;
    db.queryAsync(sql)
      .then(res1 => {
        res.send({
          code: 200,
          data: res1.results,
          msg: 'success'
        });
      })
      .catch(err => {
        handleError(res, err);
      });
  } catch (error) {
    handleError(res, error);
  }
});

// 导出
router.get('/export', (req, res) => {
  let token = req.cookies['bs-cookie'];
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');

    let { dictType, name } = req.query;
    let sql = `select * from favors`;
    if (user.role_key == 'admin') {
      if (dictType) {
        sql = `select * from favors where favors_timer = '${dictType}'`;
        if (name) {
          sql = `select * from favors where favors_timer = '${dictType}' and favors_name like '%${name}%'`;
        }
      } else {
        if (name) {
          sql = `select * from favors where favors_name like '%${name}%'`;
        }
      }
    } else {
      sql = `select * from favors where role_key = '${user.role_key}'`;
      if (dictType) {
        sql = `select * from favors where favors_timer = '${dictType}' and role_key = '${user.role_key}'`;
        if (name) {
          sql = `select * from favors where favors_timer = '${dictType}' and favors_name like '%${name}%' and role_key = '${user.role_key}'`;
        }
      } else {
        if (name) {
          sql = `select * from favors where favors_name like '%${name}%' and role_key = '${user.role_key}'`;
        }
      }
    }
    db.queryAsync(sql)
      .then(favorsRes => {
        let data = favorsRes.results;
        dictSql = `select* from sys_dict where dictType = 'favors_time' and pid <> 0 order by sort ASC;`;
        db.queryAsync(dictSql)
          .then(async favorsTimerRes => {
            let dictData = favorsTimerRes.results;
            // 处理 来往时间点 字典
            data.forEach(item => {
              item.favorsTimerStr = dictData.find(dict => dict.value == item.favors_timer)?.label || '';
              item.create_time = item.create_time ? moment(item.create_time).format('YYYY-MM-DD HH:mm:ss') : '';
              item.update_time = item.update_time ? moment(item.update_time).format('YYYY-MM-DD HH:mm:ss') : '';
            });

            const workbook = new Excel.Workbook();
            const worksheet = workbook.addWorksheet(user.role_key == 'admin' ? '人情来往明细' : user.nickname ? user.nickname + '_人情来往明细' : '人情来往明细');
            // 设置表头
            // worksheet.addRow(['姓名', '金额', '回礼', '时间点', '创建时间', '更新时间', '备注']);
            let baseTableTitle = [
              { header: '姓名', key: 'favors_name', width: 20 },
              { header: '金额', key: 'favors_money', width: 12 },
              { header: '回礼', key: 'favors_backMoney', width: 16 },
              { header: '时间点', key: 'favors_timer', width: 12 },
              { header: '所属角色', key: 'role_key', width: 12 },
              { header: '所属角色名', key: 'role_str', width: 12 },
              { header: '创建时间', key: 'create_time', width: 12 },
              { header: '更新时间', key: 'update_time', width: 12 },
              { header: '备注', key: 'remark', width: 24 }
            ];

            worksheet.columns = baseTableTitle;
            // 循环写入数据
            data.forEach(async (item, index) => {
              const rowData = worksheet.addRow([
                item.favors_name,
                item.favors_money,
                item.favors_backMoney,
                item.favorsTimerStr,
                item.role_key,
                item.role_str,
                item.create_time,
                item.update_time,
                item.remark
              ]);
              // 指定行高
              rowData.height = 22;
            });

            const buffer = await workbook.xlsx.writeBuffer();
            // 处理中文文件名
            const realName = encodeURI('人情来往明细.xlsx', 'GBK').toString('iso8859-1');
            // 设置响应头
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.setHeader('Content-Disposition', 'attachment; filename=' + realName);
            // 发送Excel文件
            res.send(buffer);
          })
          .catch(error => {
            handleError(res, error);
          });
      })
      .catch(error => {
        handleError(res, error);
      });
  } catch (error) {
    handleError(res, error);
  }
});

/**
 * @description: 上传文件
 * @param {Object} req 请求对象
 * @param {Object} res 响应对象
 * @param {Function} next 中间件函数
 */
// 配置 multer 存储
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const path = '../public/temp';
    // 检测路径文件夹是否存在 不存在则创建
    if (!fs.existsSync(path)) {
      fs.mkdirSync(path, { recursive: true });
    }
    cb(null, path);
  },
  filename: function (req, file, cb) {
    cb(null, 'favors_' + file.originalname);
  }
});

const upload = multer({ storage: storage });
// 导入
router.post('/import', upload.single('file'), (req, res) => {
  let token = req.cookies['bs-cookie'];
  const { role_key, role_str } = req.body;
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');
    const workbook = new Excel.Workbook();
    const filePath = req.file.path;
    workbook.xlsx
      .readFile(filePath)
      .then(() => {
        const worksheet = workbook.getWorksheet(1);
        const data = [];
        worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
          if (rowNumber === 1) return; // 跳过表头
          const rowData = row.values.slice(1); // 去掉第一列的索引
          data.push(rowData);
        });
        // let roleSql = `select role_key from sys_role`;
        // db.queryAsync(roleSql)
        //   .then(roleRes => {
        //     const roleData = roleRes.results;
        //     let roleKeys = roleData.map(item => item.role_key);
        //     data.forEach(item => {
        //       if (!roleKeys.includes(item[5])) {
        //         throw new Error('RoleError:角色不存在');
        //       }
        //     });
        // 处理sql语句
        let sql = 'INSERT INTO favors (favors_name, favors_money, favors_backMoney, favors_timer,  remark, role_key, role_str, create_time) VALUES ?';
        const params = data.map(item => [
          item[0],
          item[1],
          item[2],
          item[3],
          item[4],
          user.role_key == 'admin' ? role_key : user.role_key,
          user.role_key == 'admin' ? role_str : user.role_str,
          new Date()
        ]);
        db.queryAsync(sql, [params])
          .then(result => {
            res.send({
              code: 200,
              data: data,
              msg: '导入成功'
            });
          })
          .catch(err => {
            console.log(err);
            handleError(res, err);
          });
        // })
        // .catch(err => {
        //   console.log(err);
        //   handleError(res, err);
        // });
      })
      .catch(err => {
        console.log(err);
        handleError(res, err);
      });
  } catch (error) {
    handleError(res, error);
  }
});

// 导出模版
router.get('/template', async (req, res) => {
  let token = req.cookies['bs-cookie'];
  try {
    const user = verifyToken(token);
    if (!user) throw new Error('JsonWebTokenError:token验证失败');
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('人情来往模版');
    const baseTableTitle = [
      { header: '姓名', key: 'favors_name', width: 20 },
      { header: '金额', key: 'favors_money', width: 12 },
      { header: '回礼', key: 'favors_backMoney', width: 16 },
      { header: '时间点', key: 'favors_timer', width: 12 },
      { header: '备注', key: 'remark', width: 24 }
    ];
    worksheet.columns = baseTableTitle;
    // 写入一行数据作为示例数据
    const rowData = worksheet.addRow(['张三', '100', '50', '1', '新年快乐']);
    // 指定行高
    rowData.height = 22;
    const buffer = await workbook.xlsx.writeBuffer();
    const realName = encodeURI('人情来往模版.xlsx', 'GBK').toString('iso8859-1');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=' + realName);
    res.send(buffer);
  } catch (err) {
    handleError(res, err);
  }
});

module.exports = router;
